Tlustsi select v ADOQuery

Otázka od: Jaroslav Popluhar

11. 5. 2004 22:57

Hello delphi-l,

select S.*, G.*,
  (select count(AutID) from Automobil where StdID = S.StdID),
  (select count(VodID) from Vodic where StdID = S.StdID),
  (select count(JzdID) from Jazda where JzdStdID = S.StdID),
  (select count(NklID) from Naklad where StdID = S.StdID),
  (select count(UzvID) from Uzavierka where StdID = S.StdID)
from Stredisko S
left outer join Skupina G on S.SkpID = G.SkpID
where S.StdKodHS = :HS

Ked skusim vlozit tento select do ADOQuery.SQL a potom
v design time zmenit Active alebo pridat perzistentne
fieldy {Ctrl-F) tak sa na mna zacnu sypat hlasky
roznych Exception a v roznych nt..dll, oleaut.dll, msado15.dll atd.

Mam D7 Ent na WinXP-SP1, MS-SQL 2000

Ked zrusim parameter :HS alebo vnorene selecty vsetko je OK.

Nestretol sa s tym niekto?
Nepotrebujem vyssi MDAC?
Nepomaha nahodou D7 Update?

Srdecna vdaka za kazde info, som bezradny...
--
Best regards,
Jaroslav mailto:delphi1@acc.sk

Odpovedá: Lstiburek Pavel

12. 5. 2004 9:51

Pokud se na to divam, byva dobre pojmenovat sloupce:

select S.*, G.*,
   (select count(AutID) from Automobil where StdID = S.StdID) AS Automobil,
   (select count(VodID) from Vodic where StdID = S.StdID) AS Vodic,
   (select count(JzdID) from Jazda where JzdStdID = S.StdID) AS Jazda,
   (select count(NklID) from Naklad where StdID = S.StdID) AS Naklad,
   (select count(UzvID) from Uzavierka where StdID = S.StdID) AS Uzaverka
from Stredisko S
left outer join Skupina G on S.SkpID = G.SkpID
where S.StdKodHS = :HS

Pokud to neni problem prepis to na SP neco jako:
CREATE PROC dbo.selectTlustciSelect
@HS ...
AS
select S.*, G.*,
   (select count(AutID) from Automobil where StdID = S.StdID) AS Automobil,
   (select count(VodID) from Vodic where StdID = S.StdID) AS Vodic,
   (select count(JzdID) from Jazda where JzdStdID = S.StdID) AS Jazda,
   (select count(NklID) from Naklad where StdID = S.StdID) AS Naklad,
   (select count(UzvID) from Uzavierka where StdID = S.StdID) AS Uzaverka
from Stredisko S
left outer join Skupina G on S.SkpID = G.SkpIDwhere S.StdKodHS = @HS

Obecne takto postavene dotazy byvaji pomale (zalezi ale, na poctu radek ve
vyslednem selectu
pro kazhy se totiz vykona poddotaz!). Pro vetsi pocet radek se dosahuje lepsich
vysledku :

select S.*, G.*, Automobil, Vodic, Jazda, Naklad,
from Stredisko S
left outer join Skupina G on S.SkpID = G.SkpID
LEFT JOIN (select StdID, count(AutID) AS Automobil from Automobil GROUP BY
StdID) AS Automobil ON Automobil.StdID = S.StdID
LEFT JOIN (select count(VodID) Vodic from Vodic GROUP BY StdID) AS Vodic ON
Vodic.StdID = S.StdID
LEFT JOIN (select count(JzdID) Jazda from Jazda GROUP BY StdID) AS Jazda ON
Jazda.StdID = S.StdID
LEFT JOIN (select count(NklID) Naklad from Naklad GROUP BY StdID) AS Naklad ON
Naklad.StdID = S.StdID
LEFT JOIN (select count(UzvID) Uzavierka from Uzavierka GROUP BY StdID) AS
Uzavierka ON Uzavierka.StdID = S.StdID
where S.StdKodHS = @HS

Jen pro uplnost COUNT( neco) -> pocet radek s not null hodnotou ve sloupci
neco, COUNT(*) pocet radek v tabulce.
Varianta 2 je na MSSQL podstatne rychlejsi pokud je k dispozici klic.

Pavel

>
> Ked skusim vlozit tento select do ADOQuery.SQL a potom
> v design time zmenit Active alebo pridat perzistentne
> fieldy {Ctrl-F) tak sa na mna zacnu sypat hlasky

vzhledem k tomu, ze jsi nenazval sloupce tak kde chces vzit nazvy pro
perzistentni fieldy ?

> roznych Exception a v roznych nt..dll, oleaut.dll, msado15.dll atd.
>
> Mam D7 Ent na WinXP-SP1, MS-SQL 2000
>
> Ked zrusim parameter :HS alebo vnorene selecty vsetko je OK.
>
> Nestretol sa s tym niekto?
> Nepotrebujem vyssi MDAC?
> Nepomaha nahodou D7 Update?
>